﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PMQLCHH.DAO;
using System.Data;

namespace PMQLCHH.DAO
{
    class NhapHangDAO
    {
        public static DataTable LoadMaMatHang()
        {
            string sql = "Select MaMatHang from MatHang where trangthai = 1";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        
        public static DataTable LoadNhaCungCapTheoTenNhaCungCap(string tenncc)
        {
            string sql = "Select * from NhaCungCap where TrangThai=1 and TenNhaCungCap='" + tenncc + "'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LoadMatHangTheoMaMatHang(string mamh)
        {
            string sql = "Select TenMatHang, TenLoaiMatHang, TenDonVi from MatHang, LoaiMatHang, DonViTinh where MatHang.LoaiMatHang=LoaiMatHang.MaLoaiMatHang and MatHang.DonViTinh=DonViTinh.MaDonVi and MaMatHang='" + mamh + "'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static bool InSertTablePhieuNhap(PhieuNhapHangDTO pn)
        {
            string sql= String.Format("insert into PhieuNhapHang(MaPhieuNhap,MaNhaCungCap,TongSoLuongNhap,TongTien,TrangThai) values(N'{0}',N'{1}',{2},{3},{4})",pn.MaPhieuNhap,pn.MaNhaCungCap,pn.SoLuongTon,pn.TongTien,pn.TrangThai);
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable LoadDSPhieuNhap()
        {
            string sql = "select MaPhieuNhap,TenNhaCungCap,SoDienThoai,PhieuNhapHang.NgayLap as NgayLap, TongTien, CASE PhieuNhapHang.TrangThai WHEN 0 THEN N'Chưa Duyệt' ELSE N'Đã Duyệt' END AS 'TrangThai' from PhieuNhapHang, NhaCungCap where PhieuNhapHang.MaNhaCungCap=NhaCungCap.MaNhaCungCap order by NgayLap DESC";
            DataTable dt=KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable GetMaNhapHangLonNhat()
        {
            string sql = "Select top 1 MaPhieuNhap from PhieuNhapHang order by MaPhieuNhap desc";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }


        public static bool InSertTableChiTietPhieuNhap(ChiTietPhieuNhapHangDTO ctpn)
        {
            string sql = String.Format("insert into ChiTietPhieuNhapHang(MaPhieuNhap,MaMatHang,SoLuongNhap,GiaNhap,ThanhTien,TrangThai) values(N'{0}',N'{1}',{2},{3},{4},{5})", ctpn.MaPhieuNhap, ctpn.MaMatHang, ctpn.SoLuong, ctpn.GiaNhap, ctpn.ThanhTien, ctpn.TrangThai);
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool UpDateTableMatHang(ChiTietPhieuNhapHangDTO ctpn)
        {
            //Đã sửa lại-Lâm Chương Trường Thịnh
            string sql = "update MatHang set SoLuongTon=SoLuongTon+" + ctpn.SoLuong + ", GiaThamKhao=" + ctpn.GiaNhap + "where MaMatHang='" + ctpn.MaMatHang + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaTatCaTrongCTPN(ChiTietPhieuNhapHangDTO ctpn)
        {
            string sql = "delete from ChiTietPhieuNhapHang where TrangThai=0 and MaPhieuNhap='" + ctpn.MaPhieuNhap + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaTatCaTrongPN(PhieuNhapHangDTO pn)
        {
            string sql = "delete from PhieuNhapHang where TrangThai=0 and MaPhieuNhap='" + pn.MaPhieuNhap + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable LoadCTPNTheoMPN(PhieuNhapHangDTO pn)
        {
            string sql = "select ChiTietPhieuNhapHang.MaMatHang as MaMatHang,MatHang.TenMatHang as TenMatHang,LoaiMatHang.TenLoaiMatHang as LoaiMatHang,DonViTinh.TenDonVi as DonViTinh,SoLuongNhap,GiaNhap,ThanhTien";
            sql += " from MatHang,ChiTietPhieuNhapHang,LoaiMatHang,DonViTinh";
            sql += " where ChiTietPhieuNhapHang.MaMatHang=MatHang.MaMatHang and MatHang.LoaiMatHang=LoaiMatHang.MaLoaiMatHang and MatHang.DonViTinh=DonViTinh.MaDonVi and MaPhieuNhap='" + pn.MaPhieuNhap + "'";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }
        public static bool CapNhatTrangThaiPhieuNhap(PhieuNhapHangDTO pn)
        {
            string sql = "update PhieuNhapHang set TrangThai=1 where MaPhieuNhap='" + pn.MaPhieuNhap + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool CapNhatTrangThaiCTPhieuNhap(PhieuNhapHangDTO pn)
        {
            string sql = "update ChiTietPhieuNhapHang set TrangThai=1 where MaPhieuNhap='" + pn.MaPhieuNhap + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaCTPhieuNhapTheoMaPN(PhieuNhapHangDTO pn)
        {
            string sql = "delete from ChiTietPhieuNhapHang where MaPhieuNhap='" + pn.MaPhieuNhap + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static bool XoaPhieuNhapTheoMaPN(PhieuNhapHangDTO pn)
        {
            string sql = "delete from PhieuNhapHang where MaPhieuNhap='" + pn.MaPhieuNhap + "'";
            bool kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable LocPhieuNhap(string MaPN, string strTuNgay, string strDenNgay, int TrangThai)
        {
            string strQuery = "";
            strQuery = String.Format("SELECT MaPhieuNhap,TenNhaCungCap,SoDienThoai,PhieuNhapHang.NgayLap as NgayLap, TongTien, CASE PhieuNhapHang.TrangThai WHEN 0 THEN N'Chưa Duyệt' ELSE N'Đã Duyệt' END AS 'TrangThai' from PhieuNhapHang, NhaCungCap where PhieuNhapHang.MaNhaCungCap=NhaCungCap.MaNhaCungCap AND MaPhieuNhap LIKE  '%{0}%' AND PhieuNhapHang.NgayLap BETWEEN '{1}' AND '{2} 23:59:59'  ", MaPN, strTuNgay, strDenNgay);
            if (TrangThai != 2)//trang thai la chon tat ca
            {
                strQuery += String.Format("AND PhieuNhapHang.TrangThai = {0}", TrangThai);
            }
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }
        public DataTable LayNgayLapPNCuNhat()
        {
            string strQuery = "";
            strQuery = "SELECT TOP 1 NgayLap FROM PhieuNhapHang ORDER BY NgayLap ASC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }
        //------------------------------------------------------------------------------------------


    }
}
